package com.tic.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import oracle.jdbc.OracleTypes;
import utiles.AppProperties;
import utiles.ConexionPool;
import utiles.utilitarios;

import com.portal.dto.ComboDTO;//import com.far.dto.ComboDTO;
import com.portal.dto.UsuarioDTO;
import com.tic.dto.MatrizDTO;
// import com.tic.dto.UsuarioDTO; evaluar sacar comentario


public class PerfilDAO {
	private static Logger log = Logger.getLogger(PerfilDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	String horaActual = utilitarios.getHoraActual();
	
	public List<UsuarioDTO> getUsuario(String Tipo) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		List<UsuarioDTO> listUserPerf = new ArrayList<UsuarioDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getUsuario(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, Tipo);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					UsuarioDTO sopMA = new UsuarioDTO();
					sopMA.setIdUsuario(rs.getString(1));
					sopMA.setUsuario(rs.getString(2));
					sopMA.setRut(rs.getString(3));
					sopMA.setNomUsuario(rs.getString(4));
					sopMA.setSedeDire(rs.getString(5));
					sopMA.setNomArea(rs.getString(6));
					sopMA.setActivo(rs.getString(7));
					listUserPerf.add(sopMA);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error(horaActual+"Error PerfilDAO.getUsuario SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}
	
/*
	public List<MatrizDTO> getMatriz(String ida,String ids, String idm) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();

		List<MatrizDTO> listUserPerf = new ArrayList<MatrizDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getMatriz(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, ida);
				stmt.setString(2, ids);
				stmt.setString(3, idm);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					MatrizDTO sopMA = new MatrizDTO();
					sopMA.setIdmatriz(rs.getString(1));
					sopMA.setIda(rs.getString(2));
					sopMA.setArea(rs.getString(3));
					sopMA.setIds(rs.getString(4));
					sopMA.setSistema(rs.getString(5));
					sopMA.setIdm(rs.getString(6));
					sopMA.setModulo(rs.getString(7));
					sopMA.setIdp(rs.getString(8));
					sopMA.setProceso(rs.getString(9));
					
					listUserPerf.add(sopMA);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getMatriz SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	public List<ComboDTO> getArea() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getArea(?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.registerOutParameter(1, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(1);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getArea SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	public List<ComboDTO> getSistema(String id) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getSistema(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, id);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getSistema SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}
	public List<ComboDTO> getModulo(String id) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getModulo(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, id);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getModulo SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}
	
	public List<ComboDTO> getProceso() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getProceso(?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.registerOutParameter(1, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(1);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getProceso SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	public List<ComboDTO> getTipoUsuario() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getTipoUsuario(?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.registerOutParameter(1, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(1);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getSemana SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	public List<ComboDTO> setAdd(String Tipo, String Valor, String Id) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();

		

		List<ComboDTO> listArray = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.setAdd(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, Tipo);
				stmt.setString(2, Valor);
				stmt.setString(3, Id);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					
					listArray.add(perfUSR);
				}
				

	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.setAdd SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listArray;		
		}	 

	public List<MatrizDTO> setAddMatriz(String ids, String idm, String idp) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<MatrizDTO> listUserPerf = new ArrayList<MatrizDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.setAddMatriz(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, ids);
				stmt.setString(2, idm);
				stmt.setString(3, idp);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					MatrizDTO sopMA = new MatrizDTO();
					sopMA.setIdmatriz(rs.getString(1));
					sopMA.setIda(rs.getString(2));
					listUserPerf.add(sopMA);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.setAddMatriz SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}
	
	public List<MatrizDTO> getPerfilMatriz(String idtu) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<MatrizDTO> listUserPerf = new ArrayList<MatrizDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getPerfilMatriz(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, idtu);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					MatrizDTO sopMA = new MatrizDTO();
					sopMA.setIdmatriz(rs.getString(1));
					sopMA.setIda(rs.getString(2));
					sopMA.setArea(rs.getString(3));
					sopMA.setSistema(rs.getString(4));
					sopMA.setModulo(rs.getString(5));
					sopMA.setProceso(rs.getString(6));
					listUserPerf.add(sopMA);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getPerfilMatriz SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}
	
	public List<ComboDTO> getUsuarioByTipo(String id) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getUsuarioByTipo(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, id);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setId2(rs.getString(2));
					perfUSR.setDesc(rs.getString(3));
					perfUSR.setDesc2(rs.getString(4));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getUsuarioByTipo SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	public List<ComboDTO> getTipoUsuarioByMatriz(String id) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getTipoUsuarioByMatriz(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, id);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getTipoUsuarioByMatriz SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}

	public List<ComboDTO> setModificarTablas(String Tipo, String Id, String Valor) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listArray = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.setModificarTablas(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, Tipo);
				stmt.setString(2, Id);
				stmt.setString(3, Valor);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					
					listArray.add(perfUSR);
				}
				

	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.setModificarTablas SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listArray;		
		}
	
	public List<ComboDTO> setGrabarPerfil(String Tipo, String IdMatriz, String IdTipo) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();

		

		List<ComboDTO> listArray = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.setGrabarPerfil(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, Tipo);
				stmt.setString(2, IdMatriz);
				stmt.setString(3, IdTipo);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					
					listArray.add(perfUSR);
					System.out.println(horaActual+" "+rs.getString(2));
				}
				

	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.setGrabarPerfil SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listArray;		
		}	

	public List<ComboDTO> setGrabarUTU(String Tipo, String IdUser, String IdTipo) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<ComboDTO> listArray = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.setGrabarUTU(?,?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, Tipo);
				stmt.setString(2, IdUser);
				stmt.setString(3, IdTipo);
				stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(4);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setDesc(rs.getString(2));
					
					listArray.add(perfUSR);
					System.out.println(horaActual+" "+rs.getString(2));
				}
				

	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.setGrabarUTU SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listArray;		
		}
	
	
	public List<UsuarioDTO> getUsuario(String Tipo) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();


		List<UsuarioDTO> listUserPerf = new ArrayList<UsuarioDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PER_MATRIZ.getUsuario(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, Tipo);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					UsuarioDTO sopMA = new UsuarioDTO();
					sopMA.setId(rs.getString(1));
					sopMA.setUsuario(rs.getString(2));
					sopMA.setRut(rs.getString(3));
					sopMA.setNombre(rs.getString(4));
					sopMA.setSede(rs.getString(5));
					sopMA.setArea(rs.getString(6));
					sopMA.setActivo(rs.getString(7));
					listUserPerf.add(sopMA);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
				log.error("Error PerfilDAO.getUsuario SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
	        return listUserPerf;		
		}
		*/
}

